﻿using System.Collections.Generic;
using Eagles.Base.DataBase;
using Eagles.Interface.DataAccess;
using Eagles.DomainService.Model.Chart;
using System.Text;
using Dapper;

namespace Eagles.DomianService.DataAccess
{
    public class ChartDataAccess : IChartDataAccess
    {
        private readonly IDbManager dbManager;

        public ChartDataAccess(IDbManager dbManager)
        {
            this.dbManager = dbManager;
        }

        public List<TbUserSexCount> GetUserSexCount(int orgId)
        {
            return dbManager.Query<TbUserSexCount>(@"select case Sex when Sex then Sex else '其他' end as Sex,Count(*) Count from tb_user_info where OrgId = @OrgId and IsCustomer = 1 group by Sex; ", new {OrgId = orgId});
        }

        public List<TbUserEraCount> GetUserEraCount(int orgId)
        {
            return dbManager.Query<TbUserEraCount>(@"select case when year(Birthday) between 1960 and 1969 then '60' when year(Birthday) between 1970 and 1979 then '70'
when year(Birthday) between 1980 and 1989 then '80' when year(Birthday) between 1990 and 1999 then '90' when year(Birthday) between 2000 and 2009 then '00'
when year(Birthday) between 2010 and 2019 then '10' else '其他' end as Era, count(*) Count from eagles.tb_user_info where OrgId = @OrgId and IsCustomer = 1 
group by case when year(Birthday) between 1960 and 1969 then '60' 
when year(Birthday) between 1970 and 1979 then '70' when year(Birthday) between 1980 and 1989 then '80' when year(Birthday) between 1990 and 1999 then '90' 
when year(Birthday) between 2000 and 2009 then '00' when year(Birthday) between 2010 and 2019 then '10' else '其他' end order by year(Birthday); ", new {OrgId = orgId});
        }

        public List<TbUserJoinTimeCount> GetUserJoinTimeCount(int orgId)
        {
            return dbManager.Query<TbUserJoinTimeCount>(@"select case year(MemberTime) when year(MemberTime) then year(MemberTime) else '其他' end JoinTime, 
count(*) Count from eagles.tb_user_info where OrgId = @OrgId and IsCustomer = 1 group by year(MemberTime) order by year(MemberTime); ", new { OrgId = orgId });
        }

        public TbHistogram GetTaskCount(int orgId, int branchId)
        {
            var sql = new StringBuilder();
            var parameter = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            dynamicParams.Add("OrgId", orgId);
            if (branchId > 0)
            {
                parameter.Append(" and  BranchId = @BranchId ");
                dynamicParams.Add("BranchId", branchId);
            }
            sql.AppendFormat(@"select 
coalesce(sum(case month(CreateTime) when '1' then 1 else 0 end),0) as January,
coalesce(sum(case month(CreateTime) when '2' then 1 else 0 end),0) as February,
coalesce(sum(case month(CreateTime) when '3' then 1 else 0 end),0) as March,
coalesce(sum(case month(CreateTime) when '4' then 1 else 0 end),0) as April,
coalesce(sum(case month(CreateTime) when '5' then 1 else 0 end),0) as May,
coalesce(sum(case month(CreateTime) when '6' then 1 else 0 end),0) as June,
coalesce(sum(case month(CreateTime) when '7' then 1 else 0 end),0) as July,
coalesce(sum(case month(CreateTime) when '8' then 1 else 0 end),0) as August,
coalesce(sum(case month(CreateTime) when '9' then 1 else 0 end),0) as September,
coalesce(sum(case month(CreateTime) when '10' then 1 else 0 end),0) as October,
coalesce(sum(case month(CreateTime) when '11' then 1 else 0 end),0) as November,
coalesce(sum(case month(CreateTime) when '12' then 1 else 0 end),0) as December
from eagles.tb_task where year(CreateTime)=year(now()) and OrgId = @OrgId {0} ; ", parameter);
            return dbManager.QuerySingle<TbHistogram>(sql.ToString(), dynamicParams);
        }

        public TbHistogram GetTaskCompleteCount(int orgId, int branchId)
        {
            var sql = new StringBuilder();
            var parameter = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            dynamicParams.Add("OrgId", orgId);
            if (branchId > 0)
            {
                parameter.Append(" and  BranchId = @BranchId ");
                dynamicParams.Add("BranchId", branchId);
            }
            sql.AppendFormat(@"select 
coalesce(sum(case month(CreateTime) when '1' then 1 else 0 end),0) as January,
coalesce(sum(case month(CreateTime) when '2' then 1 else 0 end),0) as February,
coalesce(sum(case month(CreateTime) when '3' then 1 else 0 end),0) as March,
coalesce(sum(case month(CreateTime) when '4' then 1 else 0 end),0) as April,
coalesce(sum(case month(CreateTime) when '5' then 1 else 0 end),0) as May,
coalesce(sum(case month(CreateTime) when '6' then 1 else 0 end),0) as June,
coalesce(sum(case month(CreateTime) when '7' then 1 else 0 end),0) as July,
coalesce(sum(case month(CreateTime) when '8' then 1 else 0 end),0) as August,
coalesce(sum(case month(CreateTime) when '9' then 1 else 0 end),0) as September,
coalesce(sum(case month(CreateTime) when '10' then 1 else 0 end),0) as October,
coalesce(sum(case month(CreateTime) when '11' then 1 else 0 end),0) as November,
coalesce(sum(case month(CreateTime) when '12' then 1 else 0 end),0) as December
from eagles.tb_task where year(CreateTime)=year(now()) and Status = 3 and OrgId = @OrgId {0}; ",parameter);
            return dbManager.QuerySingle<TbHistogram>(sql.ToString(), dynamicParams);
        }

        public TbHistogram GetTaskUnCompleteCount(int orgId, int branchId)
        {
            var sql = new StringBuilder();
            var parameter = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            dynamicParams.Add("OrgId", orgId);
            if (branchId > 0)
            {
                parameter.Append(" and  BranchId = @BranchId ");
                dynamicParams.Add("BranchId", branchId);
            }
            sql.AppendFormat(@"select 
coalesce(sum(case month(CreateTime) when '1' then 1 else 0 end),0) as January,
coalesce(sum(case month(CreateTime) when '2' then 1 else 0 end),0) as February,
coalesce(sum(case month(CreateTime) when '3' then 1 else 0 end),0) as March,
coalesce(sum(case month(CreateTime) when '4' then 1 else 0 end),0) as April,
coalesce(sum(case month(CreateTime) when '5' then 1 else 0 end),0) as May,
coalesce(sum(case month(CreateTime) when '6' then 1 else 0 end),0) as June,
coalesce(sum(case month(CreateTime) when '7' then 1 else 0 end),0) as July,
coalesce(sum(case month(CreateTime) when '8' then 1 else 0 end),0) as August,
coalesce(sum(case month(CreateTime) when '9' then 1 else 0 end),0) as September,
coalesce(sum(case month(CreateTime) when '10' then 1 else 0 end),0) as October,
coalesce(sum(case month(CreateTime) when '11' then 1 else 0 end),0) as November,
coalesce(sum(case month(CreateTime) when '12' then 1 else 0 end),0) as December
from eagles.tb_task where year(CreateTime)=year(now()) and Status <> 3 and OrgId = @OrgId {0}; ", parameter);
            return dbManager.QuerySingle<TbHistogram>(sql.ToString(), dynamicParams);
        }

        public TbHistogram GetActivityCount(int orgId, int branchId)
        {
            var sql = new StringBuilder();
            var parameter = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            dynamicParams.Add("OrgId", orgId);
            if (branchId > 0)
            {
                parameter.Append(" and  BranchId = @BranchId ");
                dynamicParams.Add("BranchId", branchId);
            }
            sql.AppendFormat(@"select 
coalesce(sum(case month(CreateTime) when '1' then 1 else 0 end),0) as January,
coalesce(sum(case month(CreateTime) when '2' then 1 else 0 end),0) as February,
coalesce(sum(case month(CreateTime) when '3' then 1 else 0 end),0) as March,
coalesce(sum(case month(CreateTime) when '4' then 1 else 0 end),0) as April,
coalesce(sum(case month(CreateTime) when '5' then 1 else 0 end),0) as May,
coalesce(sum(case month(CreateTime) when '6' then 1 else 0 end),0) as June,
coalesce(sum(case month(CreateTime) when '7' then 1 else 0 end),0) as July,
coalesce(sum(case month(CreateTime) when '8' then 1 else 0 end),0) as August,
coalesce(sum(case month(CreateTime) when '9' then 1 else 0 end),0) as September,
coalesce(sum(case month(CreateTime) when '10' then 1 else 0 end),0) as October,
coalesce(sum(case month(CreateTime) when '11' then 1 else 0 end),0) as November,
coalesce(sum(case month(CreateTime) when '12' then 1 else 0 end),0) as December
from eagles.tb_activity where year(CreateTime)=year(now()) and OrgId = @OrgId {0}; ", parameter);
            return dbManager.QuerySingle<TbHistogram>(sql.ToString(), dynamicParams);
        }

        public TbHistogram GetActivityCompleteCount(int orgId, int branchId)
        {
            var sql = new StringBuilder();
            var parameter = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            dynamicParams.Add("OrgId", orgId);
            if (branchId > 0)
            {
                parameter.Append(" and  BranchId = @BranchId ");
                dynamicParams.Add("BranchId", branchId);
            }
            sql.AppendFormat(@"select 
coalesce(sum(case month(CreateTime) when '1' then 1 else 0 end),0) as January,
coalesce(sum(case month(CreateTime) when '2' then 1 else 0 end),0) as February,
coalesce(sum(case month(CreateTime) when '3' then 1 else 0 end),0) as March,
coalesce(sum(case month(CreateTime) when '4' then 1 else 0 end),0) as April,
coalesce(sum(case month(CreateTime) when '5' then 1 else 0 end),0) as May,
coalesce(sum(case month(CreateTime) when '6' then 1 else 0 end),0) as June,
coalesce(sum(case month(CreateTime) when '7' then 1 else 0 end),0) as July,
coalesce(sum(case month(CreateTime) when '8' then 1 else 0 end),0) as August,
coalesce(sum(case month(CreateTime) when '9' then 1 else 0 end),0) as September,
coalesce(sum(case month(CreateTime) when '10' then 1 else 0 end),0) as October,
coalesce(sum(case month(CreateTime) when '11' then 1 else 0 end),0) as November,
coalesce(sum(case month(CreateTime) when '12' then 1 else 0 end),0) as December
from eagles.tb_activity where year(CreateTime)=year(now()) and Status = 2 and OrgId = @OrgId {0}; ", parameter);
            return dbManager.QuerySingle<TbHistogram>(sql.ToString(), dynamicParams);
        }

        public TbHistogram GetActivityUnCompleteCount(int orgId, int branchId)
        {
            var sql = new StringBuilder();
            var parameter = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            dynamicParams.Add("OrgId", orgId);
            if (branchId > 0)
            {
                parameter.Append(" and  BranchId = @BranchId ");
                dynamicParams.Add("BranchId", branchId);
            }
            sql.AppendFormat(@"select
coalesce(sum(case month(CreateTime) when '1' then 1 else 0 end), 0) as January,
coalesce(sum(case month(CreateTime) when '2' then 1 else 0 end), 0) as February,
coalesce(sum(case month(CreateTime) when '3' then 1 else 0 end), 0) as March,
coalesce(sum(case month(CreateTime) when '4' then 1 else 0 end), 0) as April,
coalesce(sum(case month(CreateTime) when '5' then 1 else 0 end), 0) as May,
coalesce(sum(case month(CreateTime) when '6' then 1 else 0 end), 0) as June,
coalesce(sum(case month(CreateTime) when '7' then 1 else 0 end), 0) as July,
coalesce(sum(case month(CreateTime) when '8' then 1 else 0 end), 0) as August,
coalesce(sum(case month(CreateTime) when '9' then 1 else 0 end), 0) as September,
coalesce(sum(case month(CreateTime) when '10' then 1 else 0 end), 0) as October,
coalesce(sum(case month(CreateTime) when '11' then 1 else 0 end), 0) as November,
coalesce(sum(case month(CreateTime) when '12' then 1 else 0 end), 0) as December
from eagles.tb_activity where year(CreateTime) = year(now()) and Status <> 2 and OrgId = @OrgId {0}; ", parameter);
            return dbManager.QuerySingle<TbHistogram>(sql.ToString(), dynamicParams);
        }

        public TbHistogram TbUserNewsCount(int orgId, int branchId)
        {
            return dbManager.QuerySingle<TbHistogram>(@"select 
coalesce(sum(case month(CreateTime) when '1' then 1 else 0 end),0) as January,
coalesce(sum(case month(CreateTime) when '2' then 1 else 0 end),0) as February,
coalesce(sum(case month(CreateTime) when '3' then 1 else 0 end),0) as March,
coalesce(sum(case month(CreateTime) when '4' then 1 else 0 end),0) as April,
coalesce(sum(case month(CreateTime) when '5' then 1 else 0 end),0) as May,
coalesce(sum(case month(CreateTime) when '6' then 1 else 0 end),0) as June,
coalesce(sum(case month(CreateTime) when '7' then 1 else 0 end),0) as July,
coalesce(sum(case month(CreateTime) when '8' then 1 else 0 end),0) as August,
coalesce(sum(case month(CreateTime) when '9' then 1 else 0 end),0) as September,
coalesce(sum(case month(CreateTime) when '10' then 1 else 0 end),0) as October,
coalesce(sum(case month(CreateTime) when '11' then 1 else 0 end),0) as November,
coalesce(sum(case month(CreateTime) when '12' then 1 else 0 end),0) as December
from eagles.tb_user_news where year(CreateTime)=year(now()) and OrgId = @OrgId and BranchId = @BranchId; ", new { OrgId = orgId, BranchId = branchId });
        }

        public List<TbTaskCount> GetTaskCount(int orgId)
        {
            return dbManager.Query<TbTaskCount>(@"select count(*) count, b.BranchName from eagles.tb_task a inner join eagles.tb_branch b on a.BranchId = b.BranchId 
where a.OrgId = @OrgId group by BranchName; ", new { OrgId = orgId });
        }

        public List<TbActivityCount> GetActivityCount(int orgId)
        {
            return dbManager.Query<TbActivityCount>(@"select case b.BranchName when b.BranchName then b.BranchName else c.OrgName end as BranchName,Count(*) Count 
from eagles.tb_activity a left join eagles.tb_branch b on a.BranchId = b.BranchId inner join eagles.tb_org_info c on a.OrgId = c.OrgId where a.OrgId = @OrgId group by BranchName; ", new { OrgId = orgId });
        }
    }
}